﻿using System.Collections.Generic;
using System.Data;
using System.Data.OracleClient;
using TrafficArchives.Common;

namespace TrafficArchives.DataAccess
{
    /// <summary>
    /// 信誉档案管理操作类。
    /// </summary>
    public class ArchivesDb : DbBase
    {
        public override PaginationData<DataTable> GetData(Request request)
        {
            var paginationData = new PaginationData<DataTable>();
            var normalSql = "select t.*, rownum as rowno from XYDATABLE t where t.ISVALID=1 and " + request.ToSql();
            var sql = request.Pagination.Enabled
                          ? string.Format("select * from ({0}) A where A.rowno > {1} and A.rowno <= {2}", normalSql,
                                          GetPageStart(request.Pagination), GetPageEnd(request.Pagination))
                          : normalSql;
            paginationData.Data = ExecuteDataTable(sql);
            if (request.Pagination.Enabled)
            {
                paginationData.RecordCount = ExecuteCount(normalSql.Replace("t.*, rownum as rowno", "count(*)"));
            }
            return paginationData;
        }

        /// <summary>
        /// 根据Id取信誉数据。
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public override DataTable GetData(string id)
        {
            var request = new Request();
            request.Items.Add(new Condition
            {
                FieldName = "XYDATABLE_ID",
                Operator = ConditionOperator.Equal,
                Values = new object[] { id }
            });
            return GetData(request).Data;
        }

        /// <summary>
        /// 取表结构。
        /// </summary>
        /// <returns></returns>
        public override DataTable GetNoneData()
        {
            var request = new Request();
            request.Items.Add(new Condition
            {
                FieldName = "XYDATABLE_ID",
                Operator = ConditionOperator.Equal,
                Values = new object[] { "null" }
            });
            return GetData(request).Data;
        }

        public override void Add(DataTable data)
        {
            if (data.Rows.Count == 0)
            {
                return;
            }
            const string sql = "insert into XYDATABLE(XYDATABLE_ID,NAME,ZJCODE,DACODE,XYTYPEID,XYTYPENAME,CONTENT,RECODETIME,ISVALID)" +
                               "values(:XYDATABLE_ID,:NAME,:ZJCODE,:DACODE,:XYTYPEID,:XYTYPENAME,:CONTENT,:RECODETIME,:ISVALID)";
            var parameters = new[]{
                                    MakeParam(":XYDATABLE_ID", OracleType.VarChar, 40, "XYDATABLE_ID"),
                                     MakeParam(":NAME", OracleType.VarChar, 20, "NAME"),
                                     MakeParam(":ZJCODE", OracleType.VarChar, 18, "ZJCODE"),
                                     MakeParam(":DACODE", OracleType.VarChar, 18, "DACODE"),
                                     MakeParam(":XYTYPEID", OracleType.VarChar, 36, "XYTYPEID"),
                                     MakeParam(":XYTYPENAME", OracleType.VarChar, 40, "XYTYPENAME"),
                                     MakeParam(":CONTENT", OracleType.VarChar, 200, "CONTENT"),
                                     MakeParam(":RECODETIME", OracleType.DateTime, 8, "RECODETIME"),
                                     MakeParam(":ISVALID", OracleType.Int16, 8, "ISVALID")
                                  };
            OnAdd(data, sql, parameters);
        }

        public override void Update(DataTable data)
        {
            if (data.Rows.Count == 0)
            {
                return;
            }
            const string sql = "update XYDATABLE set " +
                               "NAME = :NAME," +
                               "ZJCODE = :ZJCODE," +
                               "DACODE = :DACODE," +
                               "XYTYPEID = :XYTYPEID," +
                               "XYTYPENAME = :XYTYPENAME," +
                               "CONTENT = :CONTENT," +
                               "RECODETIME = :RECODETIME," +
                               "ISVALID = :ISVALID" +
                               " where XYDATABLE_ID = :XYDATABLE_ID";
            var parameters = new[]{
                                     MakeParam(":NAME", OracleType.VarChar, 20, "NAME"),
                                     MakeParam(":ZJCODE", OracleType.VarChar, 18, "ZJCODE"),
                                     MakeParam(":DACODE", OracleType.VarChar, 18, "DACODE"),
                                     MakeParam(":XYTYPEID", OracleType.VarChar, 36, "XYTYPEID"),
                                     MakeParam(":XYTYPENAME", OracleType.VarChar, 40, "XYTYPENAME"),
                                     MakeParam(":CONTENT", OracleType.VarChar, 200, "CONTENT"),
                                     MakeParam(":RECODETIME", OracleType.DateTime, 8, "RECODETIME"),
                                     MakeParam(":ISVALID", OracleType.Int16, 8, "ISVALID"),
                                     MakeParam(":XYDATABLE_ID", OracleType.VarChar, 40, "XYDATABLE_ID")
                                  };
            OnUpdate(data, sql, parameters);
        }

        /// <summary>
        /// 取消信誉报告。
        /// </summary>
        /// <param name="ids"></param>
        public void SetNotVisible(List<string> ids)
        {
            const string sql = @"update XYDATABLE t set t.ISVALID=0 where t.XYDATABLE_ID in ('{0}')";
            ExecuteNoneQuery(string.Format(sql, string.Join("','", ids)));
        }

        public override void Delete(List<string> ids)
        {
            const string sql = "delete from XYDATABLE t where t.XYDATABLE_ID in ('{0}')";
            ExecuteNoneQuery(string.Format(sql, string.Join("','", ids)));
        }
    }
}